package org.poem.sql;

import com.google.common.collect.Lists;
import com.google.common.collect.Sets;
import org.poem.CanalClientEntryVO;
import org.poem.CanalClientHeadEntityVO;
import org.poem.StringUtils;
import org.poem.create.CanalClientCreateTableColumnEntry;
import org.poem.create.CanalClientCreateTableEntry;
import org.poem.create.CanalClientCreateTableIndexKey;
import org.poem.enums.EnumDataType;
import org.springframework.util.CollectionUtils;

import java.util.List;
import java.util.Map;
import java.util.Set;
import java.util.stream.Collectors;

import static org.poem.sql.SqlUtils.MAX_SIZE;


/**
 * @author poem
 */
public class SqlHelperHandler {


    /**
     * 创建
     *
     * @param type
     * @return
     */
    private static String getMysqlToPostgresColType(String type, String args) {
        switch (type.trim().toLowerCase()) {
            case "longtext":
            case "text":
            case "string":
            case "longblob":
                return "text";
            case "integer":
            case "int":
            case "smallint":
            case "tinyint":
                return "int4";
            case "double":
            case "float":
            case "decimal":
                return "numeric(20,2 )";
            case "long":
            case "bigint":
            case "serial":
                return "int8";
            case "timestamp":
            case "date":
            case "datetime":
            case "time":
                return "timestamp(6)";
            case "bit":
                return "bit(" + (StringUtils.isEmpty(args) ? 2 : args) + ")";
            case "varchar":
            case "char":
                return "varchar(" + (StringUtils.isEmpty(args) ? 200 : args) + ")";
            default:
                throw new RuntimeException("【Postgres】没有类型：" + type);
        }
    }

    /**
     * 创建Mysql的数据类型
     *
     * @param type
     * @return
     */
    private static String getMysqlColType(String type) {
        switch (type.trim().toLowerCase()) {
            case "string":
            case "varchar":
            case "varchar(255)":
                return "varchar(255)";
            case "integer":
            case "int":
            case "tinyint":
            case "smallint":
                return "int(20)";
            case "double":
            case "float":
            case "decimal":
                return "double(20,2 )";
            case "long":
            case "bigint":
                return "bigint(20)";
            case "timestamp":
            case "date":
            case "datetime":
                return "timestamp";
            case "bit":
                return "bit";
            case "longtext":
            case "text":
            case "char":
            case "longblob":
                return "text";
            case "numeric":
                return "numeric(20,5)";
            default:
                throw new RuntimeException("【Mysql】没有类型：" + type);
        }
    }

    /**
     * 把java类型转换为 数据库类型
     *
     * @param type         数据的类型
     * @param enumDataType 数据库的类型
     * @return 数据类型
     */
    private static String geColType(String type, EnumDataType enumDataType) {
        if (EnumDataType.POSTGRES.getType().equalsIgnoreCase(enumDataType.getType())) {
            return SqlHelperHandler.getMysqlToPostgresColType(type, null);
        }
        return SqlHelperHandler.getMysqlColType(type);
    }


    /**
     * 创建新建表的sql
     *
     * @param tableName    表的名字
     * @param colsDataType 表的列类型关系
     * @return
     */
    static String getCreateMysqlTableSql(String tableName, Map<String, String> colsDataType, Map<String, String> colsIndexKey) {
        StringBuilder sqlStr = new StringBuilder("-- ----------------------------\n");
        sqlStr.append("-- Table structure for ").append(tableName).append("\n");
        sqlStr.append("-- ----------------------------\n");
        sqlStr.append("CREATE TABLE IF NOT EXISTS  `").append(tableName).append("` (").append("\n\t\t");
        List<String> colunmsList = Lists.newArrayList();
        Set<String> keyList = Sets.newHashSet();

        colsDataType.forEach(
                (k, v) -> {
                    String type = SqlHelperHandler.getMysqlColType(v);
                    if (colsIndexKey != null && !CollectionUtils.isEmpty(colsIndexKey)) {
                        if (null != colsIndexKey.get(k) && type.toLowerCase().contains("varchar")) {
                            type = "varchar(50)";
                        }
                    }
                    if (type.toLowerCase().contains("timestamp")) {
                        colunmsList.add("`" + k + "`  timestamp  NULL DEFAULT NULL ");
                    } else {
                        colunmsList.add("`" + k + "`  " + type + " DEFAULT NULL ");
                    }
                    if (colsIndexKey != null && !CollectionUtils.isEmpty(colsIndexKey)) {
                        if (null != colsIndexKey.get(k)) {
                            keyList.add(k);
                        }
                    }
                }
        );

        if (!CollectionUtils.isEmpty(keyList)) {
            for (String s : keyList) {
                colunmsList.add("KEY `" + tableName + "_" + s + "` (`" + s + "`)");
            }
        }
        sqlStr.append(String.join(",\n\t\t", colunmsList));
        sqlStr.append(" \n)ENGINE=MyISAM ").append(";").append("\n");
        return sqlStr.toString();
    }


    /**
     * 长安postgres的sql
     *
     * @param tableName    表
     * @param colsDataType 类型
     * @return 创建表的sql
     */
    static String getPostgresTableSql(String tableName, Map<String, String> colsDataType, Map<String, String> colsIndexKey) {
        StringBuilder sqlStr = new StringBuilder("-- ----------------------------\n");
        sqlStr.append("-- Table structure for ").append(tableName).append("\n");
        sqlStr.append("-- ----------------------------\n");
        sqlStr.append("CREATE TABLE IF NOT EXISTS  \"public\".\"").append(tableName).append("\" (").append("\n\t\t");
        List<String> colunmsList = Lists.newArrayList();
        List<String> keyList = Lists.newArrayList();
        colsDataType.forEach(
                (k, v) -> {
                    if ("timestamp".equalsIgnoreCase(String.valueOf(v))) {
                        colunmsList.add("\"" + k + "\"  timestamp(6)");
                    } else {
                        colunmsList.add("\"" + k + "\"  " + geColType(v, EnumDataType.POSTGRES) + " DEFAULT NULL ");
                    }
                    if (colsIndexKey != null && !CollectionUtils.isEmpty(colsIndexKey)) {
                        if (null != colsIndexKey.get(String.valueOf(v)) && "PRI".equals(String.valueOf(String.valueOf(v)))) {
                            keyList.add(String.valueOf(v));
                        }
                    }
                }
        );
        sqlStr.append(String.join(",\n\t\t", colunmsList));
        sqlStr.append(" \n)").append(";").append("\n");
        List<String> indexStr = Lists.newArrayList();
        if (!CollectionUtils.isEmpty(keyList)) {
            for (String s : keyList) {
                indexStr.add(" CREATE INDEX " + tableName + "_" + s + " ON " + tableName + " (" + s + ");");
            }
        }
        sqlStr.append(String.join("\n", indexStr));
        return sqlStr.toString();
    }

    /**
     * 获取插入到hive数据库的sql
     *
     * @param metadata
     * @param colnums
     * @param colunmTypes
     * @param tableName
     * @return
     */
    private static List<String> createHiveInsertSql(List<Map<String, Object>> metadata, List<String> colnums, List<String> colunmTypes, String tableName) {
        List<String> insertLists = Lists.newArrayList();
        Map<String, String> typeZip = SqlUtils.zipColumnTypes(colnums, colunmTypes);
        for (Map<String, Object> metadatum : metadata) {
            StringBuilder stringBuilder = new StringBuilder();
            StringBuilder sql = stringBuilder.append(" INSERT  INTO ").append(tableName).append(" VALUES ( ");
            for (String colnum : colnums) {
                Object data = metadatum.get(colnum);
                String type = typeZip.get(colnum);
                if ("Long".equalsIgnoreCase(type)
                        || "Integer".equalsIgnoreCase(type)
                        || "Double".equalsIgnoreCase(type)
                        || "Float".equalsIgnoreCase(type)
                        || "BigDecimal".equalsIgnoreCase(type)
                ) {
                    if ("".equals(data) || data == null) {
                        data = 0;
                    }
                    sql.append(data);
                } else {
                    //去掉分号
                    if (data instanceof String) {
                        if ("".equals(data) || "null".equals(data)) {
                            sql.append("null");
                        } else {
                            String item = ((String) data).replaceAll(";", "，").replaceAll("'", " ");
                            sql.append("'");
                            sql.append(item);
                            sql.append("'");
                        }
                    } else {
                        if (data == null || "".equals(data) || "null".equals(data)) {
                            sql.append("null");
                        } else {
                            sql.append("'");
                            sql.append(data);
                            sql.append("'");

                        }
                    }
                }
                sql.append(",");
            }
            sql.append(")");
            sql.replace(sql.lastIndexOf(","), sql.lastIndexOf(",") + 1, "");
            insertLists.add(sql.toString());
        }

        return insertLists;
    }


    /**
     * 创建mysql的插入sql
     *
     * @param table     表
     * @param metadata  数据列
     * @param columns   列
     * @param typeZip   类型
     * @param currentNo 当前的版本
     * @return
     */
    static List<String> createMysqlInsertSql(String table,
                                             List<Map<String, Object>> metadata,
                                             List<String> columns,
                                             Map<String, String> typeZip,
                                             String currentNo) {
        List<String> insertLists = Lists.newArrayList();
        int sequence = 0;
        Map<String, Object> metadatum;
        StringBuilder sql = new StringBuilder();
        for (int index = 0; index < metadata.size(); index++) {
            metadatum = metadata.get(index);
            if (metadatum == null) {
                continue;
            }
            if (index % (MAX_SIZE / 2) == 0) {
                if (index != 0) {
                    sql.replace(sql.lastIndexOf(","), sql.lastIndexOf(",") + 1, "");
                    insertLists.add(sql.toString());
                }
                sql = new StringBuilder().append(" INSERT  INTO `").append(table).append("` (`")
                        .append(String.join("`,`", columns)).append("`").append(")")
                        .append(" VALUES  ");
                sequence = 0;
            }
            sql.append("(");
            for (int i = 0; i < columns.size(); i++) {
                String column = columns.get(i);
                if ("_rownumber".equals(column)) {
                    continue;
                }
                String type = typeZip.get(column);
                Object data = metadatum.get(column);
                if ("1539252108886".equals(String.valueOf(data))) {
                    Integer.valueOf("11");
                }
                if ("Long".equalsIgnoreCase(type)
                        || "Integer".equalsIgnoreCase(type)
                        || "Float".equalsIgnoreCase(type)
                        || "Double".equalsIgnoreCase(type)
                        || "BigDecimal".equalsIgnoreCase(type)

                ) {
                    if ("".equals(data) || data == null) {
                        data = 0;
                    }
                    sql.append(data);
                } else if ("tinyint".equalsIgnoreCase(type)) {
                    //bool类型
                    sql.append(data);
                } else if ("bit".equalsIgnoreCase(type)) {
                    //bool类型
                    if (data != null && ("true".equalsIgnoreCase(String.valueOf(data)) || "false".equalsIgnoreCase(String.valueOf(data)))) {
                        boolean b = Boolean.getBoolean(String.valueOf(data));
                        sql.append(b ? 1 : 0);
                    } else if (null == data) {
                        sql.append("null");
                    }

                } else {
                    //去掉分号
                    if (data instanceof String) {
                        if ("".equals(data) || "null".equals(data)) {
                            sql.append("null");
                        } else {
                            String item = ((String) data).replaceAll(";", "，").replaceAll("'", " ");
                            if (item.endsWith("\\")) {
                                item = item.replaceAll("\\\\", "\\\\\\\\");
                            }
                            sql.append("'").append(item).append("'");
                        }
                    } else {
                        if (data == null || "".equals(data) || "null".equals(data)) {
                            sql.append("null");
                        } else {
                            sql.append("'").append(data).append("'");
                        }
                    }
                }
                if (i != columns.size() - 1) {
                    sql.append(",");
                }
            }
            sql.append(")");
            if (sequence == 0 || index % (MAX_SIZE / 2) != 0) {
                sql.append(",");
            }
            sequence++;
        }
        if (sql.lastIndexOf(",") != -1) {
            sql.replace(sql.lastIndexOf(","), sql.lastIndexOf(",") + 1, "");
        }
        insertLists.add(sql.toString());
        return insertLists;
    }


    /**
     * 创建mysql的插入sql
     *
     * @param table     表
     * @param metadata  数据列
     * @param columns   列
     * @param typeZip   类型
     * @param currentNo 当前的版本
     * @return
     */
    static List<String> createPostInsertSql(String table,
                                            List<Map<String, Object>> metadata,
                                            List<String> columns,
                                            Map<String, String> typeZip,
                                            String currentNo) {
        List<String> insertLists = Lists.newArrayList();
        int sequence = 0;
        Map<String, Object> metadatum;
        StringBuilder sql = new StringBuilder();
        for (int index = 0; index < metadata.size(); index++) {
            metadatum = metadata.get(index);
            if (metadatum == null) {
                continue;
            }
            if (index % (MAX_SIZE / 2) == 0) {
                if (index != 0) {
                    sql.replace(sql.lastIndexOf(","), sql.lastIndexOf(",") + 1, "");
                    insertLists.add(sql.toString());
                }
                sql = new StringBuilder().append(" INSERT  INTO public.").append(table).append(" (\"")
                        .append(String.join("\",\"", columns)).append("\")")
                        .append(" VALUES  ");
                sequence = 0;
            }
            sql.append("(");
            for (int i = 0; i < columns.size(); i++) {
                String column = columns.get(i);
                String type = typeZip.get(column);
                Object data = metadatum.get(column);
                if ("Long".equalsIgnoreCase(type)
                        || "Integer".equalsIgnoreCase(type)
                        || "Float".equalsIgnoreCase(type)
                        || "Double".equalsIgnoreCase(type)
                        || "BigDecimal".equalsIgnoreCase(type)

                ) {
                    if ("".equals(data) || data == null) {
                        data = 0;
                    }
                    sql.append(data);
                } else if ("tinyint".equalsIgnoreCase(type)) {
                    //bool类型
                    if (data == null) {
                        sql.append("null");
                    } else {
                        sql.append("'").append("0".equals(String.valueOf(data)) ? 0 : 1).append("'");
                    }
                } else {
                    //去掉分号
                    if (data instanceof String) {
                        if ("".equals(data) || "null".equals(data)) {
                            sql.append("null");
                        } else {
                            String item = ((String) data).replaceAll(";", "，").replaceAll("'", " ");
                            if (item.endsWith("\\")) {
                                item = item.replaceAll("\\\\", "\\\\\\\\");
                            }
                            sql.append("'").append(item).append("'");
                        }
                    } else {
                        if (data == null || "".equals(data) || "null".equals(data)) {
                            sql.append("null");
                        } else {
                            sql.append("'").append(data).append("'");
                        }
                    }
                }
                if (i != columns.size() - 1) {
                    sql.append(",");
                }
            }
            sql.append(")");
            if (sequence == 0 || index % (MAX_SIZE / 2) != 0) {
                sql.append(",");
            }
            sequence++;
        }
        if (sql.lastIndexOf(",") != -1) {
            sql.replace(sql.lastIndexOf(","), sql.lastIndexOf(",") + 1, "");
        }
        insertLists.add(sql.toString());
        return insertLists;
    }

    /**
     * 创建postgres create table sql
     *
     * @param createTableEntries 表描述信息
     * @return 创建表的sql
     */
    public static List<String> getPostgresCreateTableSql(List<CanalClientCreateTableEntry> createTableEntries) {
        List<String> createTableSql = Lists.newArrayList();
        for (CanalClientCreateTableEntry createTableEntry : createTableEntries) {
            StringBuilder sqlStr = new StringBuilder("-- ----------------------------\n");
            sqlStr.append("-- Table structure for ").append(createTableEntry.getTableName()).append("\n");
            sqlStr.append("-- ----------------------------\n");
            sqlStr.append("CREATE TABLE IF NOT EXISTS  \"public\".\"").append(createTableEntry.getTableName()).append("\" (").append("\n\t\t");
            List<String> colunmsList = Lists.newArrayList();
            // column
            for (CanalClientCreateTableColumnEntry columnEntry : createTableEntry.getColumnEntries()) {
                if (!CollectionUtils.isEmpty(columnEntry.getDataTypeArguments())) {
                    colunmsList.add("\"" + columnEntry.getColumnName() + "\"  " + getMysqlToPostgresColType(columnEntry.getDataTypeName(), columnEntry.getDataTypeArguments().stream().map(String::valueOf).collect(Collectors.joining(",")))
                            + " DEFAULT NULL ");
                } else {
                    colunmsList.add("\"" + columnEntry.getColumnName() + "\"  " + getMysqlToPostgresColType(columnEntry.getDataTypeName(), null) + " DEFAULT NULL ");
                }
            }

            //primary key
            if (!CollectionUtils.isEmpty(createTableEntry.getPrimaryKeyColumn())) {
                colunmsList.add(" CONSTRAINT \"" + createTableEntry.getTableName() + "_pkey\" PRIMARY KEY (\"" + String.join(",", createTableEntry.getPrimaryKeyColumn()) + "\")");
            }
            sqlStr.append(String.join(",\n\t\t", colunmsList));
            sqlStr.append(" \n)").append(";").append("\n");
            List<String> comment = Lists.newArrayList();
            //comment
            for (CanalClientCreateTableColumnEntry columnEntry : createTableEntry.getColumnEntries()) {
                comment.add("COMMENT ON COLUMN \"public\".\"" + createTableEntry.getTableName() + "\".\"" + columnEntry.getColumnName() + "\" IS \'" + columnEntry.getComment().replaceAll("'", "") + "';\n");
            }
            sqlStr.append(String.join("\n", comment));

            //index key
            for (CanalClientCreateTableIndexKey tableIndexKey : createTableEntry.getIndexKeyList()) {
                List<String> index = Lists.newArrayList();
                for (String s : tableIndexKey.getColumnName()) {
                    index.add("\"" + s + "\" COLLATE \"pg_catalog\".\"default\" \"pg_catalog\".\"text_ops\" ASC NULLS LAST");
                }
                sqlStr.append("CREATE INDEX \"name_index\" ON \"public\".\"").append(createTableEntry.getTableName()).append("\" USING btree (\n")
                        .append(String.join(",\n", index))
                        .append(");\n");
            }
            createTableSql.add(sqlStr.toString().replace("`", ""));
        }
        return createTableSql;
    }

    /**
     * 创建删除表的sql
     *
     * @param createTableEntries 信息
     * @return
     */
    public static List<String> getPostgresDeleteTableSql(List<CanalClientCreateTableEntry> createTableEntries) {
        List<String> createTableSql = Lists.newArrayList();
        for (CanalClientCreateTableEntry createTableEntry : createTableEntries) {
            createTableSql.add("DROP TABLE IF EXISTS \"" + createTableEntry.getTableName() + "\"");
        }
        return createTableSql;
    }

    /**
     * 创建mysql的更新语句
     *
     * @param entry                   key
     * @param canalClientHeadEntityVO 对象
     * @return sql
     */
    static String getUpdateMysqlTableByKey(CanalClientEntryVO entry, CanalClientHeadEntityVO canalClientHeadEntityVO) {
        List<String> updateSql = Lists.newArrayList();
        for (CanalClientEntryVO beforeEntry : canalClientHeadEntityVO.getEntries()) {
            updateSql.add("`" + beforeEntry.getName() + "`='" + beforeEntry.getValue() + "'");
        }
        StringBuilder update = new StringBuilder().append("UPDATE ")
                .append(canalClientHeadEntityVO.getTableName())
                .append(" SET ")
                .append(String.join(",", updateSql));
        if (entry != null) {
            update.append(" WHERE `").append(entry.getName()).append("`='").append(entry.getValue()).append("'");
        } else {
            List<String> where = Lists.newArrayList();
            for (CanalClientEntryVO beforeEntry : canalClientHeadEntityVO.getBeforeEntries()) {
                where.add("`" + beforeEntry.getName() + "`='" + beforeEntry.getValue() + "'");
            }
            update.append(" WHERE 1=1 AND ").append(String.join(" AND ", where));
        }
        return update.toString();
    }

    /**
     * 创建postgres的语句
     *
     * @param entry                   key
     * @param canalClientHeadEntityVO 对象
     * @return sql
     */
    static String getUpdatePostgresTableByKey(CanalClientEntryVO entry, CanalClientHeadEntityVO canalClientHeadEntityVO) {
        List<String> updateSql = Lists.newArrayList();
        for (CanalClientEntryVO beforeEntry : canalClientHeadEntityVO.getEntries()) {
            updateSql.add("\"" + beforeEntry.getName() + "\"='" + beforeEntry.getValue() + "'");
        }
        StringBuilder update = new StringBuilder().append("UPDATE ")
                .append("public.").append(canalClientHeadEntityVO.getTableName())
                .append(" SET ")
                .append(String.join(",", updateSql));
        if (entry != null) {
            update.append(" WHERE \"").append(entry.getName()).append("\"='").append(entry.getValue()).append("'");
        } else {
            List<String> where = Lists.newArrayList();
            for (CanalClientEntryVO beforeEntry : canalClientHeadEntityVO.getBeforeEntries()) {
                where.add("\"" + beforeEntry.getName() + "\"='" + beforeEntry.getValue() + "'");
            }
            update.append(" WHERE 1=1 AND ").append(String.join(" AND ", where));
        }
        return update.toString();
    }

    /**
     * mysql的删除语句
     *
     * @param entry                   key
     * @param canalClientHeadEntityVO 对象
     * @return sql
     */
    static String getDeleteMysqlTableByKey(CanalClientEntryVO entry, CanalClientHeadEntityVO canalClientHeadEntityVO) {
        if (entry != null) {
            return "DELETE FROM `" + canalClientHeadEntityVO.getTableName() + "` " +
                    "WHERE " + entry.getName() + "='" + entry.getValue() + "'";
        } else {
            StringBuilder sql = new StringBuilder("DELETE FROM `" + canalClientHeadEntityVO.getTableName() + "` " +
                    "WHERE 1=1  ");
            List<String> where = Lists.newArrayList();
            if (canalClientHeadEntityVO.getEntries() != null) {
                for (CanalClientEntryVO beforeEntry : canalClientHeadEntityVO.getEntries()) {
                    where.add("`" + beforeEntry.getName() + "`='" + beforeEntry.getValue() + "'");
                }
            }

            return CollectionUtils.isEmpty(where) ? sql.toString() :
                    sql.append(" AND ").append(String.join(" AND ", where)).toString();
        }
    }

    /**
     * postgres的删除语句
     *
     * @param entry                   key
     * @param canalClientHeadEntityVO 对象
     * @return sql
     */
    static String getDeletePostgresTableByKey(CanalClientEntryVO entry, CanalClientHeadEntityVO canalClientHeadEntityVO) {
        if (entry != null) {
            return "DELETE FROM \"public\".\"" + canalClientHeadEntityVO.getTableName() + "\" " +
                    "WHERE \"" + entry.getName() + "\"='" + entry.getValue() + "'";
        } else {
            StringBuilder sql = new StringBuilder("DELETE FROM \"public\".\"" + canalClientHeadEntityVO.getTableName() + "\" " +
                    "WHERE 1=1  ");
            List<String> where = Lists.newArrayList();
            for (CanalClientEntryVO beforeEntry : canalClientHeadEntityVO.getEntries()) {
                where.add("\"" + beforeEntry.getName() + "\"='" + beforeEntry.getValue() + "'");
            }
            return CollectionUtils.isEmpty(where) ? sql.toString() :
                    sql.append(" AND ").append(String.join(" AND ", where)).toString();
        }
    }
}
